Skip to main content

03-05 declaring-the-parameters-Subs-and-Functions

declaring the parameters (Subs and Functions)​

When creating a new Sub or Function you are able to determine the inputs to your new creation. There are a handful of ways of handling the inputs:

  • Put the inputs into the parameters of the Sub/Function and allow the caller to provide them
  • Use knowledge of the spreadsheet to determine the inputs (or prompt the user for an input)

The main split here is: do you require the person typing the VBA to give you the inputs? Or, do you use some other approach like asking the user or just pulling the inputs from the spreadsheet.

The most common approach is to pull the inputs out of the spreadsheet. This seems counter intuitive, but if you consider that the vast majority of VBA code is purposes written for a single use, then it stands to reason that code will not be built on a large number of Subs/Functions accepting parameters. The reason for this is that generally someone writes VBA to handle their spreadsheet and so the VBA just reflects that spreadsheet. This works great for individual cases but can become a burden when building larger workflows. The main thing to consider for lager workflows is that as the complexity grows, there will be a large amount og code that is called multiple times or could be called separately from the main workflow. When the sis the case, you are often served by pulling that code out into its own Sub/Function with parameters.

To create a Sub or Function with parameters, you simply add them to the definition line:

Sub WithSomeName(firstParameter as String)

End Sub

This approach is very simple. You give the parameter a name and a type declaration. This is very nice because it nearly exactly matches the Dim statement with a Sub. That correspondence makes it very easy to start with an internally declared variable and then upgrade it to parameter. You can also go the other way: take a parameter and inline it into the Sub with some default or determined value. This is less common.

Once the parameter has been given a name and a type, you can simply use it within the Sub like any other variable. In this regard, your code will look the exact same. IF you are the person typing the VBA to use this Sub, then you will have to provide an appropriate variable as the parameter to make it all work.

declaring an Optional parameter​

The one additional thing to consider is that of Optional parameters. An optional parameter is one who is not strictly required. In lieu of a value, you can either leave the parameter missing or provide a default value. In either case, you can use the VBA specific function IsMissing() to determine if the parameter was entered. An Optional parameter can be a very nice feature when you are trying to determine whether or not to make a Sub take parameters or just use defaults. You can provide the defaults in the parameter declaration and then allow the user (person typing the VBA) to override them if needed. This is a very common approach when writing library type code; provide sensible defaults that can be overwritten.